using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for CDBAccess
/// </summary>
public class CDBAccess
{
        private string strConnectString;
        private SqlConnection objConnect;
        private SqlDataAdapter objAdapter;
        private SqlCommand objCommand;
        public CDBAccess()
        {
            //strConnectString = "Integrated Security=SSPI;Initial Catalog=BidWeb;Data Source=localhost";
            //strConnectString = "server=localhost;database=BidWeb;uid='tynv';pwd='1'";
           // strConnectString = Configuration.ConfigurationManager.AppSettings("LocalConnection").ToString();
            //strConnectString = WebConfigurationManager.ConnectionStrings[0].ConnectionString;
            strConnectString =WebConfigurationManager.AppSettings["LocalConnection"].ToString();            
            objConnect = new SqlConnection(strConnectString);
            objCommand = new SqlCommand();
            objAdapter = new SqlDataAdapter();
        }
        public void Connect()
        {
            try
            {
                if (objConnect.State == ConnectionState.Open)
                    objConnect.Close();
                objConnect = new SqlConnection(strConnectString);
                objConnect.Open();                
            }
            catch
            {
                objConnect.Dispose();
            }
        }          
        public void Disconnect()
        {
            if (objConnect.State != ConnectionState.Closed)
                objConnect.Close();
        }
        public DataSet GetDataSetSql(string strSQL)
        {
            Connect();
            objAdapter = new SqlDataAdapter(strSQL, objConnect);
            DataSet ds = new DataSet();
            objAdapter.Fill(ds);
            return ds;
        }       
        public DataSet GetDataSetStore(string strStoreName, string strParamater, string strValue)
        {
            DataSet ds = new DataSet();
            try
            {
                objCommand.CommandText = strStoreName;
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Clear();
                GetParamater(strParamater, strValue);                
                Connect();
                objCommand.Connection = objConnect;
                objAdapter = new SqlDataAdapter(objCommand);
                objAdapter.Fill(ds, "Result");
            }

            catch
            {
                ds = null;
            }
            return ds;
           
            

        }      
        public int ExecuteSql(string Sql)
        {
            int result = 0;
            Connect();
            objCommand = new SqlCommand(Sql, objConnect);
            result = objCommand.ExecuteNonQuery();
            Disconnect();
            return result;
        }      
        public int ExecuteStore(string strStoreName, string strParamater, string strValue)
        {
            try
            {
                //nhan gia tri cac tham so
                //luu gia tri vao doi tuong objCommand
                objCommand.CommandText = strStoreName;
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Clear();                
                GetParamater(strParamater, strValue);
                Connect();
                objCommand.Connection = objConnect;
                int result;
                result = objCommand.ExecuteNonQuery();
                return result;
            }
            catch (Exception ex)
            {
                return 0;
            }
           
        }
        public void GetParamater(string strParamater, string strValue)
        {
            string[] arrParamater = strParamater.Split(new char[] { '~' });
            string[] arrValue = strValue.Split(new char[] { '~' });
            string strParaname = "";
            string strParavalue = "";
            for (int i = 0; i < arrParamater.Length; i++)
            {
                strParaname = arrParamater[i].ToString();
                strParavalue = arrValue[i].ToString();
                if (strParaname != "")
                {
                    objCommand.Parameters.Add(strParaname, SqlDbType.NVarChar).Value = strParavalue;
                }
            }

        }
        public int ExecuteStoreScalar(string strStoreName, string strParamater, string strValue)
        {
            try
            {
                //nhan gia tri cac tham so
                //luu gia tri vao doi tuong objCommand
                objCommand.CommandText = strStoreName;
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Clear();
                GetParamater(strParamater, strValue);
                Connect();
                objCommand.Connection = objConnect;
                object result;
                result = objCommand.ExecuteScalar();
                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                return 0;
            }

        }
	}
